﻿CREATE VIEW dbo.VIEW_SEARCH_P_U_FOR_FORMS
AS
SELECT     dbo.PRODUCT_UNIT.ID, dbo.PRODUCT_UNIT.NAME, dbo.NSI_PRODUCT_STATE.NAME AS NSI_PRODUCT_STATE_NAME, dbo.PRODUCT_UNIT.TYPE, 
                      dbo.PRODUCT_COST.COST, dbo.getPUInvSumCur(dbo.PRODUCT_UNIT.ID) AS INVOICE_IN_SUM, dbo.getPUPaySumCur(dbo.PRODUCT_UNIT.ID) 
                      AS PAYMENT_IN_SUM_CUR, dbo.getPUPaySumRub(dbo.PRODUCT_UNIT.ID) AS PAYMENT_IN_SUM_RUB, 
                      dbo.NSI_CURRENCY.SHORT_NAME AS NSI_CURRENCY_NAME, dbo.COST_PROTOCOL.NUMBER AS COST_PROTOCOL_NUMBER, 
                      dbo.PRODUCT_UNIT.CP_STAGE_ID, dbo.CALENDAR_PLAN.NUMBER + N' \ №' + SPACE(1) + dbo.CP_STAGE.NUMBER + SPACE(1) 
                      + dbo.CP_STAGE.NAME AS CP_STAGE_NUMBER, dbo.CP_STAGE.START_DATE AS CP_STAGE_START_DATE, dbo.CP_STAGE.END_DATE AS CP_STAGE_END_DATE, 
                      dbo.PRODUCT_UNIT.PID, dbo.PRODUCT_UNIT.NSI_PRODUCT_STATE_ID, dbo.PRODUCT_UNIT.NSI_PRODUCT_ID, 
                      dbo.GUILD_BILL.NUMBER AS GUILD_BILL_NUMBER, dbo.DIRECT.NUMBER AS DIRECT_NUMBER, dbo.PRODUCT_UNIT.DIRECT_NUMBER AS DIRECT_NUMBER_ID, 
                      dbo.PRODUCT_UNIT.GUILD_BILL_NUMBER AS GUILD_BILL_NUMBER_ID, dbo.PRODUCT_UNIT.VAT_INVOICE_ID, 
                      100 * dbo.getPUPaySumCur(dbo.PRODUCT_UNIT.ID) / dbo.PRODUCT_COST.COST AS PAY_PER, dbo.VAT_INVOICE.NUMBER AS VAT_INVOICE_NUMBER, 
                      dbo.NSI_METER.SHORT_NAME AS NSI_METER_NAME
FROM         dbo.CALENDAR_PLAN INNER JOIN
                      dbo.CP_STAGE INNER JOIN
                      dbo.COST_PROTOCOL INNER JOIN
                      dbo.PRODUCT_COST ON dbo.COST_PROTOCOL.ID = dbo.PRODUCT_COST.COST_PROTOCOL_ID INNER JOIN
                      dbo.PRODUCT_UNIT ON dbo.PRODUCT_COST.PRODUCT_UNIT_ID = dbo.PRODUCT_UNIT.ID ON dbo.CP_STAGE.ID = dbo.PRODUCT_UNIT.CP_STAGE_ID INNER JOIN
                      dbo.NSI_PRODUCT_STATE ON dbo.PRODUCT_UNIT.NSI_PRODUCT_STATE_ID = dbo.NSI_PRODUCT_STATE.ID INNER JOIN
                      dbo.NSI_CURRENCY ON dbo.COST_PROTOCOL.NSI_CURRENCY_ID = dbo.NSI_CURRENCY.ID ON 
                      dbo.CALENDAR_PLAN.ID = dbo.CP_STAGE.CALENDAR_PLAN_ID LEFT OUTER JOIN
                      dbo.NSI_METER RIGHT OUTER JOIN
                      dbo.NSI_PRODUCT ON dbo.NSI_METER.ID = dbo.NSI_PRODUCT.NSI_METER_ID ON 
                      dbo.PRODUCT_UNIT.NSI_PRODUCT_ID = dbo.NSI_PRODUCT.ID LEFT OUTER JOIN
                      dbo.VAT_INVOICE ON dbo.PRODUCT_UNIT.VAT_INVOICE_ID = dbo.VAT_INVOICE.ID LEFT OUTER JOIN
                      dbo.GUILD_BILL ON dbo.PRODUCT_UNIT.GUILD_BILL_NUMBER = dbo.GUILD_BILL.ID LEFT OUTER JOIN
                      dbo.DIRECT ON dbo.PRODUCT_UNIT.DIRECT_NUMBER = dbo.DIRECT.ID
GROUP BY dbo.PRODUCT_UNIT.CP_STAGE_ID, dbo.PRODUCT_UNIT.TYPE, dbo.PRODUCT_UNIT.NAME, dbo.PRODUCT_COST.COST, dbo.CP_STAGE.NUMBER, 
                      dbo.CP_STAGE.NAME, dbo.CP_STAGE.START_DATE, dbo.CP_STAGE.END_DATE, dbo.PRODUCT_UNIT.PID, dbo.COST_PROTOCOL.NUMBER, 
                      dbo.NSI_PRODUCT_STATE.NAME, dbo.PRODUCT_UNIT.NSI_PRODUCT_STATE_ID, dbo.PRODUCT_UNIT.ID, dbo.NSI_CURRENCY.SHORT_NAME, 
                      dbo.PRODUCT_UNIT.NSI_PRODUCT_ID, dbo.GUILD_BILL.NUMBER, dbo.DIRECT.NUMBER, dbo.COST_PROTOCOL.ID, dbo.DIRECT.ID, dbo.GUILD_BILL.ID, 
                      dbo.PRODUCT_UNIT.DIRECT_NUMBER, dbo.PRODUCT_UNIT.GUILD_BILL_NUMBER, dbo.PRODUCT_UNIT.VAT_INVOICE_ID, dbo.VAT_INVOICE.ID, 
                      dbo.VAT_INVOICE.NUMBER, dbo.NSI_METER.SHORT_NAME, dbo.getPUInvSumCur(dbo.PRODUCT_UNIT.ID), dbo.getPUPaySumRub(dbo.PRODUCT_UNIT.ID), 
                      dbo.getPUPaySumCur(dbo.PRODUCT_UNIT.ID), 100 * dbo.getPUPaySumCur(dbo.PRODUCT_UNIT.ID) / dbo.PRODUCT_COST.COST, 
                      dbo.CALENDAR_PLAN.NUMBER + N' \ №' + SPACE(1) + dbo.CP_STAGE.NUMBER + SPACE(1) + dbo.CP_STAGE.NAME